package br.com.atibrasil.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import br.com.atibrasil.fx.controller.PadraoForm;
import br.com.atibrasil.model.C100;
import br.com.atibrasil.model.C170;
import br.com.atibrasil.model.C190;
import br.com.atibrasil.model.Empresa;
import br.com.atibrasil.model.Estado;
import br.com.atibrasil.model.G110;
import br.com.atibrasil.model.G125;
import br.com.atibrasil.model.H010;
import br.com.atibrasil.model.Parametro;
import br.com.atibrasil.model.R0150;
import br.com.atibrasil.model.R0200;
import br.com.atibrasil.model.R0300;
import br.com.atibrasil.model.R0305;
import br.com.atibrasil.model.R0500;
import br.com.atibrasil.model.R0600;

public class RegistroDAO {
	protected Connection con;
	protected Statement stm;
	protected ResultSet rs;
	protected SimpleDateFormat sdf = new SimpleDateFormat("ddMMyyyy");
	
	public void getConection() throws ClassNotFoundException, SQLException{
	     String url ="jdbc:postgresql://localhost/ATIBRASIL"; 
	     String usuario="postgres"; 
	     String senha = "root"; 	      	      
	     Class.forName("org.postgresql.Driver"); 			
		 con = DriverManager.getConnection(url,usuario,senha);	    						
	     stm = con.createStatement();	
	}	
	
	public Empresa getEmpresa() throws SQLException, ParseException, ClassNotFoundException{
		Empresa retorno = new Empresa();
		retorno.setListaC100(new ArrayList<C100>());
		retorno.setListaC190(new ArrayList<C190>());
		StringBuffer sb = new StringBuffer();
		getConection();
		
		HashMap<String, R0150> hashR0150 = new HashMap<String, R0150>();
		sb.append("SELECT C1, C4, C5, C6, C7 FROM DADOS where Registro = '0150'");
		ResultSet rs = stm.executeQuery(sb.toString());
		while (rs.next()) {
			R0150 r0150 = new R0150();
			r0150.setCodPart(rs.getString("C1"));
			if (rs.getString("C4") != null && !rs.getString("C4").equals("")) {
				r0150.setCnpj(rs.getString("C4"));				
			}else{
				r0150.setCnpj(rs.getString("C5"));
			}			
			r0150.setInscricaoEstadual(rs.getString("C6"));
			String municipio = rs.getString("C7");
			if (municipio != null && !municipio.equals("")){
				r0150.setMunicipio(municipio);				
				Estado estado = PadraoForm.hashEstado.get(municipio.substring(0, 2));
				if (estado == null) {
					estado = new Estado();
					estado.setDescricao("??");
				}
				r0150.setUf(estado);
			}
			hashR0150.put(r0150.getCodPart().trim(), r0150);
		}
		sb = new StringBuffer();	
		sb.append("SELECT Registro, C1, C2, C3, C4, C5, C6, C7, C8, C10, linha_arquivo FROM DADOS where Registro IN ('C100', 'C190') ORDER BY Ordem, c2, c9");				     				     				     
		rs = stm.executeQuery(sb.toString());  
		while (rs.next())
		{			
			if (rs.getString("Registro").equals("C100")){
				C100 c100 = new C100(); 
				String date = rs.getString("C10");
				if (date != null && !date.equals("")) {
					c100.setEmissao(sdf.parse(date));
				}
				c100.setNumero(rs.getString("C7"));
				c100.setListaC190(new ArrayList<C190>());
				R0150 r0150 = hashR0150.get(rs.getString("C3").trim());
				if (r0150 == null) {
					r0150 = new R0150();
					r0150.setCnpj("");
				}
				c100.setR150(r0150);
				c100.setSerie(rs.getString("C6"));
				c100.setOperacao(rs.getString("C1"));
				c100.setModelo(rs.getString("C4"));
				c100.setEmitente(rs.getString("C2"));
				retorno.getListaC100().add(c100);
			}else if (rs.getString("Registro").equals("C190")){
				C190 c190 = new C190();
				c190.setCfop(rs.getString("C2"));
				c190.setValorTotal(Double.parseDouble(rs.getString("C4").replace(",",".")));
				c190.setBaseCalculoICMS(Double.parseDouble(rs.getString("C5").replace(",",".")));
				c190.setIcms(Double.parseDouble(rs.getString("C6").replace(",",".")));
				c190.setBaseCalculoICMSST(Double.parseDouble(rs.getString("C7").replace(",",".")));
				c190.setIcmsST(Double.parseDouble(rs.getString("C8").replace(",",".")));
				c190.setIpi(Double.parseDouble(rs.getString("C10").replace(",",".")));
				c190.setLinha(rs.getString("linha_arquivo"));
				if (rs.getString("C3") != null && !rs.getString("C3").equals("")) {
					c190.setAliquota(Double.parseDouble(rs.getString("C3").replace(",",".")));
				}else{
					c190.setAliquota(0);
				}
				C100 c100 = retorno.getListaC100().get(retorno.getListaC100().size() - 1);
				c190.setC100(c100);				
				c100.getListaC190().add(c190);
				retorno.getListaC190().add(c190);
			}										    					   
		}
		con.close();
		stm.close();
		rs.close();
		return retorno;
	}	
	
	
	
	public Empresa getDadosEmpresa(Parametro parametro) throws SQLException, ParseException, ClassNotFoundException{
		Empresa retorno = new Empresa();
		retorno.setListaC100(new ArrayList<C100>());
		retorno.setListaC190(new ArrayList<C190>());
		StringBuffer sb = new StringBuffer();
		getConection();
		
		HashMap<String, R0150> hashR0150 = new HashMap<String, R0150>();
		HashMap<String, R0300> hashR0300 = new HashMap<String, R0300>();
		HashMap<String, R0500> hashR0500 = new HashMap<String, R0500>();
		HashMap<Integer, R0305> hashR0305 = new HashMap<Integer, R0305>();
		HashMap<String, R0600> hashR0600 = new HashMap<String, R0600>();
		HashMap<String, R0200> hashR0200 = new HashMap<String, R0200>();		
		
		sb.append("SELECT C1, C4, C5, C6, C7 FROM DADOS where Registro = '0150'");
		ResultSet rs = stm.executeQuery(sb.toString());
		while (rs.next()) {
			R0150 r0150 = new R0150();
			r0150.setCodPart(rs.getString("C1"));
			if ( rs.getString("C4") != null && !rs.getString("C4").equals("") ) {
				r0150.setCnpj(rs.getString("C4"));				
			}else{
				r0150.setCnpj(rs.getString("C5"));				
			}
			r0150.setInscricaoEstadual(rs.getString("C6"));
			String municipio = rs.getString("C7");
			if (municipio != null && !municipio.equals("")){
				r0150.setMunicipio(municipio);				
				Estado estado = PadraoForm.hashEstado.get(municipio.substring(0, 2));
				if (estado == null) {
					estado = new Estado();
					estado.setDescricao("??");
				}
				r0150.setUf(estado);
			}
			hashR0150.put(r0150.getCodPart().trim(), r0150);		
		}
		
		sb = new StringBuffer();	
		sb.append("SELECT C1, C2, C7 FROM DADOS WHERE REGISTRO = '0200'");
	    rs = stm.executeQuery(sb.toString());
		while (rs.next()) {
			R0200 r0200 = new R0200();
			r0200.setCodigoItem(rs.getString("C1"));
			r0200.setDescricao(rs.getString("C2"));
			r0200.setNBM(rs.getString("C7"));
			hashR0200.put(r0200.getCodigoItem().trim(), r0200);			
		}
		
		sb = new StringBuffer();
		
		// Incluir os registros 490
		if (parametro != null && parametro.incluirC490) {
			sb.append("SELECT Ordem, Registro, C1, C2, C3, C4, C5, C6, C7, C8, C9, C10, C13, C19 FROM DADOS where Registro IN ('C100', 'C190', 'D100', 'D190', 'C460', 'C490', 'C170') ORDER BY Ordem, c2, c9");			
		}else{
			sb.append("SELECT Ordem, Registro, C1, C2, C3, C4, C5, C6, C7, C8, C9, C10, C13, C19 FROM DADOS where Registro IN ('C100', 'C190', 'D100', 'D190', 'C170') ORDER BY Ordem, c2, c9");			
		}				     				     				     
		rs = stm.executeQuery(sb.toString());  
		while (rs.next())
		{			
			if (rs.getString("Registro").equals("C100") || rs.getString("Registro").equals("D100") || rs.getString("Registro").equals("C460") ){
				C100 c100 = new C100(); 
				c100.setListaC190(new ArrayList<C190>());
				c100.setListaC170(new ArrayList<C170>());				
				c100.setLinha(rs.getString("Ordem"));
				c100.setRegistro(rs.getString("Registro"));
				if (rs.getString("Registro").equals("C460")){
					c100.setModelo(rs.getString("C1"));
					c100.setOperacao("1");										
					String date = rs.getString("C4");
					if (date != null && !date.equals("")) {
						c100.setEmissao(sdf.parse(date));
					}					
					c100.setNumero(rs.getString("C3"));
					R0150 r0150 = new R0150();
					r0150.setUf(new Estado());
					r0150.setCnpj(rs.getString("C8"));
					c100.setR150(r0150);
				}else{
						String date = rs.getString("C10");
						if (date != null && !date.equals("")) {
							c100.setEmissao(sdf.parse(date));
						}
						if (rs.getString("Registro").equals("C100")) { 
							c100.setNumero(rs.getString("C7"));
							//Novos
							c100.setChaveNFE(rs.getString("C8"));
							if (rs.getString("C13") != null && !rs.getString("C13").equals("")) {
								c100.setDescontos(Double.parseDouble(rs.getString("C13").replace(",",".")));
							}else{	
								c100.setDescontos(0);						
							}
							if (rs.getString("C19") != null && !rs.getString("C19").equals("")){
								c100.setDespesas(Double.parseDouble(rs.getString("C19").replace(",",".")));						
							}else{
								c100.setDespesas(0);
							}																
						}else{
							c100.setNumero(rs.getString("C8"));
						}
						R0150 r0150 = hashR0150.get(rs.getString("C3").trim());
						if (r0150 == null) {
							r0150 = new R0150();
							r0150.setCnpj("");
						}else{
							r0150.setCnpj(r0150.getCnpj());
						}
						c100.setR150(r0150);
						c100.setSerie(rs.getString("C6"));
						c100.setOperacao(rs.getString("C1"));
						c100.setModelo(rs.getString("C4"));
						c100.setEmitente(rs.getString("C2"));	
				}
				retorno.getListaC100().add(c100);
			}else if (rs.getString("Registro").equals("C190") || rs.getString("Registro").equals("D190") || rs.getString("Registro").equals("C490") ){
				C190 c190 = new C190();
				c190.setRegistro(rs.getString("Registro"));
				if (rs.getString("Registro").equals("C490")){
						c190.setCfop(rs.getString("C2"));
						if (rs.getString("C3") != null && !rs.getString("C3").equals("")) {
							c190.setAliquota(Double.parseDouble(rs.getString("C3").replace(",",".")));
						}else{
							c190.setAliquota(0);
						}						
						c190.setValorTotal(Double.parseDouble(rs.getString("C4").replace(",",".")));
						c190.setCfop(rs.getString("C2"));						
				}else{
						c190.setCfop(rs.getString("C2"));
						c190.setValorTotal(Double.parseDouble(rs.getString("C4").replace(",",".")));
						c190.setBaseCalculoICMS(Double.parseDouble(rs.getString("C5").replace(",",".")));
						c190.setIcms(Double.parseDouble(rs.getString("C6").replace(",",".")));
						c190.setBaseCalculoICMSST(Double.parseDouble(rs.getString("C7").replace(",",".")));
						if (rs.getString("C8") != null && !rs.getString("C8").equals("")) {			
							c190.setIcmsST(Double.parseDouble(rs.getString("C8").replace(",",".")));
						}else{
							c190.setIcmsST(0);
						}
						
						if (rs.getString("C10") != null && !rs.getString("C10").equals("")) {			
							c190.setIpi(Double.parseDouble(rs.getString("C10").replace(",",".")));
						}else{
							c190.setIpi(0);
						}				
						if (rs.getString("C3") != null && !rs.getString("C3").equals("")) {
							c190.setAliquota(Double.parseDouble(rs.getString("C3").replace(",",".")));
						}else{
							c190.setAliquota(0);
						}
						if (rs.getString("Registro").equals("C190")) { 
							//Novo
							c190.setIsentas(Double.parseDouble(rs.getString("C9").replace(",",".")));
						}
				}				
				C100 c100 = retorno.getListaC100().get(retorno.getListaC100().size() - 1);
				c190.setC100(c100);				
				c100.getListaC190().add(c190);
				retorno.getListaC190().add(c190);
			}else if (rs.getString("Registro").equals("C170")){
				C170 c170 = new C170();	
				c170.setLinha(rs.getString("Ordem"));
				c170.setItem(rs.getString("C1"));
				c170.setCodigoItem(rs.getString("C2"));
				c170.setDescricao(rs.getString("C3"));
				c170.setCfop(rs.getString("C10"));
				c170.setQuantidade(Double.parseDouble(rs.getString("C4").replace(",",".")));												
				C100 c100 = retorno.getListaC100().get(retorno.getListaC100().size() - 1);
				c170.setC100(c100);		
				c170.setR0200(hashR0200.get(c170.getCodigoItem().trim()));
				c100.getListaC170().add(c170);							
			}
		}
		
		if (parametro != null){
			if (parametro.isCiap()) {
				// Hash Map Conta Analitica
				sb = new StringBuffer();	
				sb.append("select c5 as COD_CTA, c6 as NOME_CTA ");				     				     				     
				sb.append("from dados where registro = '0500'");
				rs = stm.executeQuery(sb.toString());
				hashR0500 = new HashMap<String, R0500>();
				while (rs.next())
				{			
					R0500 r0500 = new R0500();
					String chave = rs.getString("COD_CTA");
					r0500.setContaAnalitica(rs.getString("COD_CTA"));
					r0500.setDescricaoConta(rs.getString("NOME_CTA"));
					hashR0500.put(chave, r0500);
				}	
				// Centro de Custo
				sb = new StringBuffer();	
				sb.append("select c2 as COD_CCUS, c3 as CCUS ");				     				     				     
				sb.append("from dados where registro = '0600'");
				rs = stm.executeQuery(sb.toString());
				hashR0600 = new HashMap<String, R0600>();
				while (rs.next())
				{			
					R0600 r0600 = new R0600();
					String chave = rs.getString("COD_CCUS");
					r0600.setCodigoCentroCusto(chave);
					r0600.setDescricaoCentroCusto(rs.getString("CCUS"));			
					hashR0600.put(chave, r0600);
				}					
				
				//Validade
				sb = new StringBuffer();	
				sb.append("select ORDEM, c1 as COD_CCUS, c2 as FUNC, c3 as VIDA_UTIL ");				     				     				     
				sb.append("from dados where registro = '0305'");
				rs = stm.executeQuery(sb.toString());
				hashR0305 = new HashMap<Integer, R0305>();
				while (rs.next())
				{			
					R0305 r0305 = new R0305();
					int chave = Integer.parseInt(rs.getString("ORDEM"));
					r0305.setLinhaArquivo(chave);
					r0305.setCodigoCentroCusto(rs.getString("COD_CCUS"));
					r0305.setVidaUtil(Integer.parseInt(rs.getString("VIDA_UTIL")));	
					r0305.setFuncaoBem(rs.getString("FUNC"));
					r0305.setR0600(hashR0600.get(r0305.getCodigoCentroCusto()));
					hashR0305.put(chave, r0305);
				}									
				// HashMap Item
				sb = new StringBuffer();	
				sb.append("select ORDEM, c1 as COD_IND_BEM, c2 as IND_MERC, c3 as DESCR_ITEM, c4 as COD_PRNC, c5 as COD_CTA ");				     				     				     
				sb.append("FROM dados where registro = '0300'");
				rs = stm.executeQuery(sb.toString());
				hashR0300 = new HashMap<String, R0300>();
				while (rs.next())
				{			
					R0300 r0300 = new R0300();
					String chave = rs.getString("COD_IND_BEM");
					r0300.setLinhaArquivo(Integer.parseInt(rs.getString("ORDEM")));
					r0300.setCodigoIndividualizado(chave);
					r0300.setCodigoBemPrincipal(rs.getString("COD_PRNC"));
					r0300.setContaAnalitica(rs.getString("COD_CTA"));
					r0300.setDescricaoBem(rs.getString("DESCR_ITEM"));
					r0300.setIndMerc(rs.getString("IND_MERC"));
					r0300.setR0500(hashR0500.get(r0300.getContaAnalitica()));
					r0300.setR0305(hashR0305.get(r0300.getLinhaArquivo() + 1));
					hashR0300.put(chave, r0300);
				}
				
				sb = new StringBuffer();	
				sb.append("SELECT ");				     				     				     
				sb.append("c1 as COD_IND_BEM, c2 as DT_MOV, c3 as TIPO_MOV, c4 as VL_IMOB_ICMS_OP, c5 as VL_IMOB_ICMS_ST, c6 as VL_IMOB_ICMS_FRT, ");
				sb.append("c7 as VL_IMOB_ICMS_DIF, c8 as NUM_PARC, c9 as VL_PARC_PASS ");
				sb.append("FROM dados where registro = 'G125'");
				rs = stm.executeQuery(sb.toString());
				List<G125> listaG125 = new ArrayList<G125>();
				double somaVlPass = 0;
				while (rs.next())
				{			
					G125 g125 = new G125();
					g125.setCodigoIndividualizado(rs.getString("COD_IND_BEM"));
					g125.setDataMovimentacao(sdf.parse(rs.getString("DT_MOV")));
					g125.setTipoMovimentacao(rs.getString("TIPO_MOV"));					
					g125.setValorImobICMSDIF(Double.parseDouble(rs.getString("VL_IMOB_ICMS_DIF").replace(",",".")));
					g125.setValorImobICMSFRT(Double.parseDouble(rs.getString("VL_IMOB_ICMS_FRT").replace(",",".")));
					g125.setValorImobICMSOP(Double.parseDouble(rs.getString("VL_IMOB_ICMS_OP").replace(",",".")));
					g125.setValorImobICMSST(Double.parseDouble(rs.getString("VL_IMOB_ICMS_ST").replace(",",".")));
					g125.setNumParcela(Double.parseDouble(rs.getString("NUM_PARC").replace(",",".")));
					g125.setValorParcPass(Double.parseDouble(rs.getString("VL_PARC_PASS").replace(",",".")));
					g125.setR0300(hashR0300.get(g125.getCodigoIndividualizado()));
					somaVlPass = somaVlPass + g125.getValorParcPass();
					listaG125.add(g125);

				}											
				sb = new StringBuffer();	
				sb.append("SELECT ");				     				     				     
				sb.append("ordem, registro, c1 as DT_INI, c2 as DT_FIN, c3 as SALDO_IN_ICMS, c4 as SOM_PARC, c5 as VL_TRIB_EXP, c6 as VL_TOTAL, ");
				sb.append("c7 as IND_PER_SAI, ");
				sb.append("c8 as ICMS_APROP, ");
				sb.append(" c9 as SOM_ICMS_OC ");				
				sb.append("FROM dados where registro = 'G110'");
				rs = stm.executeQuery(sb.toString()); 
				List<G110> listaG110 = new ArrayList<G110>();
				while (rs.next())
				{			
					G110 g110 = new G110();
					g110.setDataInicio(sdf.parse(rs.getString("DT_INI")));
					g110.setDataFim(sdf.parse(rs.getString("DT_FIN")));
					g110.setSaldoInicialICMS(Double.parseDouble(rs.getString("SALDO_IN_ICMS").replace(",",".")));
														
					g110.setSomaICMSApropriadoOutros(Double.parseDouble(rs.getString("SOM_ICMS_OC").replace(",",".")));
					g110.setSomaParcelaICMS(Double.parseDouble(rs.getString("SOM_PARC").replace(",",".")));
					g110.setValorICMSApropriado(Double.parseDouble(rs.getString("ICMS_APROP").replace(",",".")));
					g110.setValorIndiceParticipacao(Double.parseDouble(rs.getString("IND_PER_SAI").replace(",",".")));
					g110.setValorTotal(Double.parseDouble(rs.getString("VL_TOTAL").replace(",",".")));
					g110.setValorTributadoExportacao(Double.parseDouble(rs.getString("VL_TRIB_EXP").replace(",",".")));					
					g110.setListaG125(listaG125);
					listaG110.add(g110);
				}
				retorno.setListaG110(listaG110);
			}
		}		
		con.close();
		stm.close();
		rs.close();
		return retorno;
	}		
	
	public Empresa getInventarioEmpresa(Parametro parametro) throws SQLException, ParseException, ClassNotFoundException{
		Empresa retorno = new Empresa();
		StringBuffer sb = new StringBuffer();
		getConection();		
		HashMap<String, R0150> hashR0150 = new HashMap<String, R0150>();
		HashMap<String, R0200> hashR0200 = new HashMap<String, R0200>();		
		
		sb.append("SELECT C1, C2, C4, C5, C6, C7 FROM DADOS where Registro = '0150'");
		ResultSet rs = stm.executeQuery(sb.toString());
		while (rs.next()) {
			R0150 r0150 = new R0150();
			r0150.setCodPart(rs.getString("C1"));
			if ( rs.getString("C4") != null && !rs.getString("C4").equals("") ) {
				r0150.setCnpj(rs.getString("C4"));				
			}else{
				r0150.setCnpj(rs.getString("C5"));				
			}
			r0150.setNomeEmpresarial(rs.getString("C2"));
			r0150.setInscricaoEstadual(rs.getString("C6"));
			String municipio = rs.getString("C7");
			if (municipio != null && !municipio.equals("")){
				r0150.setMunicipio(municipio);				
				Estado estado = PadraoForm.hashEstado.get(municipio.substring(0, 2));
				if (estado == null) {
					estado = new Estado();
					estado.setDescricao("??");
				}
				r0150.setUf(estado);
			}
			hashR0150.put(r0150.getCodPart().trim(), r0150);		
		}		
		
		sb = new StringBuffer();	
		sb.append("SELECT C1, C2, C7 FROM DADOS WHERE REGISTRO = '0200'");
	    rs = stm.executeQuery(sb.toString());
		while (rs.next()) {
			R0200 r0200 = new R0200();
			r0200.setCodigoItem(rs.getString("C1"));
			r0200.setDescricao(rs.getString("C2"));
			r0200.setNBM(rs.getString("C7"));
			hashR0200.put(r0200.getCodigoItem().trim(), r0200);			
		}		
		sb = new StringBuffer();
		sb.append("SELECT REGISTRO, C1, C2, C3, C4, C5, C6, C7, C8, C9, ordem FROM DADOS WHERE registro = 'H010' order by ordem");							     				     				     
		rs = stm.executeQuery(sb.toString());
		List<H010> listaH010 = new ArrayList<H010>();
		while (rs.next()){
			H010 h010 = new H010();
			h010.setRegistro(rs.getString("REGISTRO"));
			h010.setCodigoItem(rs.getString("C1"));			
			h010.setR0200(hashR0200.get(h010.getCodigoItem()));
			h010.setUnidade(rs.getString("C2"));
			if (rs.getString("C3") != null && !rs.getString("C3").equals("")) {
				h010.setQuantidade(Double.parseDouble(rs.getString("C3").replace(",",".")));
			}
			h010.setValorUnitarioItem(rs.getString("C4"));				
			if (rs.getString("C5") != null && !rs.getString("C5").equals("")){				
				h010.setValorItem(Double.parseDouble(rs.getString("C5").replace(",",".")));
			}					
			h010.setIndicadorPropriedadePosse(rs.getString("C6"));
			h010.setCodigoParticipante(rs.getString("C7"));
			h010.setR0150(hashR0150.get(rs.getString("C7")));
			listaH010.add(h010);
		}	
		retorno.setListaH010(listaH010);
		con.close();
		stm.close();
		rs.close();
		return retorno;
	}			
	
	
}
